import React, {Component} from 'react';
import Markdown from 'react-markdown';
import {Anchor, Row, Col} from 'antd';

const {Link} = Anchor;

class JsPage extends Component {
    constructor(props) {
        super(props);
    }

    render() {

        return (
            <div style={{background:"#c0d2bc"}}>
                <Row>
                    <Col span={19}>
                        <h3 id="exists"> exists 和 not exists </h3>
                        <Markdown source={"表1：\n" +
                        "\n" +
                        "| ID   |  Name  |\n" +
                        "| :--------: | :--------:|\n" +
                        "| 1  | A1 |\n" +
                        "| 2  | A2 |\n" +
                        "| 3  | A3 |\n" +
                        "\n" +
                        "表2：\n" +
                        "\n" +
                        "| ID   |  AID  | Name  |\n" +
                        "| :--------: | :--------:| :--------: |\n" +
                        "| 1  |   1 | B1 |\n" +
                        "| 2  |   2 | B2 |\n" +
                        "| 3  |   2 | B3 |\n" +
                        "\n" +
                        "执行sql：\n" +
                        "\n" +
                        "    SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)\n" +
                        "执行结果为\n" +
                        "1 A1\n" +
                        "2 A2\n" +
                        "\n" +
                        "| ID   |  Name  |\n" +
                        "| :--------: | :--------:|\n" +
                        "| 1  | A1 |\n" +
                        "| 2  | A2 |\n" +
                        "\n" +
                        "\n" +
                        "\n" +
                        "    SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)\n" +
                        "| ID   |  Name  |\n" +
                        "| :--------: | :--------:|\n" +
                        "| 3  | A3 |\n" +
                        "\n" +
                        "in 和exists\n" +
                        "in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。\n" +
                        "\n" +
                        "一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。\n" +
                        "\n" +
                        "**如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：**\n" +
                        "\n" +
                        "例如：\n" +
                        "\n" +
                        "表A（小表），表B（大表）1：\n" +
                        "select * from A where exists(select cc from B where cc=A.cc)\n" +
                        "\n" +
                        "表A（大表），表B（小表）2：\n" +
                        "select * from A where cc in (select cc from B)\n" +
                        "\n" +
                        "\n" +
                        "考虑如下SQL语句\n" +
                        "\n" +
                        "1.select * from A where exists (select * from B where B.id = A.id);\n" +
                        "\n" +
                        "2.select * from A where A.id in (select id from B);\n" +
                        "\n" +
                        " \n" +
                        "\n" +
                        "查询1.可以转化以下伪代码，便于理解\n" +
                        "\n" +
                        "for ($i = 0; $i < count(A); $i++) {\n" +
                        "\n" +
                        "　　$a = get_record(A, $i); #从A表逐条获取记录\n" +
                        "\n" +
                        "　　if (B.id = $a[id]) #如果子条件成立\n" +
                        "\n" +
                        "　　　　$result[] = $a;\n" +
                        "\n" +
                        "}\n" +
                        "\n" +
                        "大概就是这么个意思，其实可以看到,查询1主要是用到了B表的索引，A表如何对查询的效率影响应该不大\n" +
                        "\n" +
                        "查询2.可以转换为\n" +
                        "\n" +
                        "select * from A where A.id = 1 or A.id = 2 or A.id = 3;\n" +
                        "\n" +
                        "这个好理解了，这里主要是用到了A的索引，B表如何对查询影响不大\n" +
                        "\n" +
                        "所以，如果B表比较A表大，应该使用B表的索引;\n" +
                        "\n" +
                        "如果A表比B表大，就应该使用A表的索引;\n" +
                        "\n" +
                        "\n" +
                        "not in 和not exists\n" +
                        "如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；\n" +
                        "\n" +
                        "**而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists 都比not in 要快。**\n" +
                        "\n" +
                        "下面再看not exists 和 not in\n" +
                        " \n" +
                        " 1.select * from A where not exists (select * from B where B.id = A.id);\n" +
                        " \n" +
                        " 2.select * from A where A.id not in (select id from B);\n" +
                        " \n" +
                        " 看查询1，还是和上面一样，用了B的索引\n" +
                        " \n" +
                        " 而对于查询2，可以转化成如下语句\n" +
                        " \n" +
                        " select * from A where A.id != 1 and A.id != 2 and A.id != 3;\n" +
                        " \n" +
                        " 可以知道not in是个范围查询，这种!=的范围查询无法使用任何索引,等于说A表的每条记录，都要在B表里遍历一次，查看B表里是否存在这条记录\n" +
                        " \n" +
                        " 故not exists比not in效率高\n" +
                        " \n" +
                        "\n" +
                        "\n" }/>


                        <h3 id="ref"> 参考 </h3>
                        <Markdown source={"https://www.cnblogs.com/beijingstruggle/p/5885137.html"}/>


                    </Col>
                    <Col span={5}>
                        <Anchor showInkInFixed="true" style={{background:"#ebefe0"}}>
                            <Link href="#exists" title="exists 和not exists"/>
                            <Link href="#ref" title="参考"/>

                        </Anchor>
                    </Col>
                </Row>
            </div>

        );
    }
}

export default JsPage;